﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management;
using System.Data;
using System.Windows.Forms;


namespace DBCodeGenerator
{
    public delegate void OnStubComplete(string procname);

    public class CSharpGenerator
    {
        public string SelectedDatabase { get; set; }
        public string Prefix { get; set; }
        public string ClassName { get; set; }
        public string clNamespace { get; set; }
        public List<CustomStoredProcedure> StordProcData { get; set; }

        public const string RETURNTYPE_DS = "DataSet";
        public const string RETURNTYPE_BOOL = "bool";
        public const string RETURNTYPE_SINGLEVALUE = "object";

        private Server CurrentServer;

        public event OnStubComplete StubComplete;


        public CSharpGenerator()
        {
            CurrentServer = GetMsSqlServer(GlobalSettings.Uname, GlobalSettings.Password, GlobalSettings.ServerName);
            StubComplete = new OnStubComplete(stcomplete);
        }

        private void stcomplete(string pname) { }

        public List<string> GetAllDbs()
        {
            List<string> alldb = new List<string>();
            Server CurrentServer = GetMsSqlServer(GlobalSettings.Uname, GlobalSettings.Password, GlobalSettings.ServerName);
            if (CurrentServer != null)
            {
                foreach (Database db in CurrentServer.Databases)
                {
                    alldb.Add(db.Name);
                }
            }

            return alldb;
        }

        public void GetStoredProc()
        {
            List<CustomStoredProcedure> allprocs = new List<CustomStoredProcedure>();
            Database seldb = CurrentServer.Databases[SelectedDatabase];
            foreach (StoredProcedure StoredProc in seldb.StoredProcedures)
            {
                if (!StoredProc.IsSystemObject)
                {
                    CustomStoredProcedure p = new CustomStoredProcedure();
                    p.ID = StoredProc.ID.ToString();
                    p.Name = StoredProc.Name;
                    p.ReturnType = "None";
                    allprocs.Add(p);
                }
            }

            StordProcData = allprocs;
        }

        public void ReadDatabases()
        {
            Database seldb = CurrentServer.Databases[SelectedDatabase];
            CsharpFunctionStub cshfuncstub = null;
            foreach (StoredProcedure myStoredProcedure in seldb.StoredProcedures)
            {
                if (!myStoredProcedure.IsSystemObject)
                {
                    List<CsharpFunctionStub.StubParam> stubparms = new List<CsharpFunctionStub.StubParam>();
                    cshfuncstub = new CsharpFunctionStub();

                    foreach (Parameter item in myStoredProcedure.Parameters)
                    {
                        CsharpFunctionStub.StubParam stubparm = new CsharpFunctionStub.StubParam();
                        stubparm.name = item.Name.Replace("@", "");
                        stubparm.type = item.DataType;
                        stubparms.Add(stubparm);
                    }
                    CustomStoredProcedure std = StordProcData.Find(p => p.ID == myStoredProcedure.ID.ToString());
                    string rettype = "";

                    if (std.ReturnType == "None")
                        rettype = RETURNTYPE_BOOL;
                    else if (std.ReturnType == "Muiltiple Rows")
                        rettype = RETURNTYPE_DS;
                    else if (std.ReturnType == "Single Row")
                        rettype = RETURNTYPE_DS;
                    else if (std.ReturnType == "Single Value")
                        rettype = RETURNTYPE_SINGLEVALUE;
                    else
                        rettype = RETURNTYPE_DS;

                    cshfuncstub.FileName = ClassName + ".cs";
                    if (this.Prefix.EndsWith("_"))
                        cshfuncstub.FunctionName = this.Prefix + myStoredProcedure.Name;
                    else
                        cshfuncstub.FunctionName = this.Prefix + "_" + myStoredProcedure.Name;
                    cshfuncstub.Parameters = stubparms;
                    cshfuncstub.stdProcname = myStoredProcedure.Name;
                    cshfuncstub.ReturnType = rettype;
                    cshfuncstub.GenerateFunction();
                    StubComplete(myStoredProcedure.Name);
                }
            }

            if (cshfuncstub != null)
                cshfuncstub.GenerateClass(this.ClassName, this.clNamespace);


        }

        private Server GetMsSqlServer(string username, string password, string servername)
        {
            try
            {
                if (!String.IsNullOrEmpty(servername))
                {
                    Server myServer = new Server(servername);
                    myServer.ConnectionContext.LoginSecure = false;
                    myServer.ConnectionContext.Login = username;
                    myServer.ConnectionContext.Password = password;
                    myServer.ConnectionContext.Connect();
                    return myServer;
                }
                else
                {
                    throw new Exception("You need to login to the database server!");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }

        }

    }
}
